﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPoco;
using Creative85.Framework.Data;
using Creative85.Framework.Utilities;
using Project32CHE.Biz.Goods.Model;
using Project32CHE.Biz.Goods.Extend.Model;
using Project32CHE.Biz.Unionshop;
using Project32CHE.Biz.Unionshop.Models;
namespace Project32CHE.Biz.Goods.Extend
{
    partial class InventoryService
    {
        /// <summary>
        /// 根据商品ID获取库存记录
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public static List<StorageItem> GetStorageItem(Guid shopId, Guid goodsId)
        {
            var list = Db.Instance.Query<StorageItem>().Where(x => (x.ShopID == shopId && x.GoodsID == goodsId)).ToList();
            return list;
        }
        public static List<StorageItem> GetStorageItem(Guid shopId, Guid goodsId, Guid storage)
        {
            var list = Db.Instance.Query<StorageItem>().Where(x => (x.ShopID == shopId && x.StorageID == storage && x.GoodsID == goodsId)).ToList();
            return list;
        }
        /// <summary>
        /// 商品库存分布
        /// </summary>
        /// <param name="unionID"></param>
        /// <param name="shopId"></param>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> GoodsBranch(Guid unionID, Guid shopId, Guid goodsId)
        {
            var sql = new SqlBuilder();
            var sqlTpl = sql.AddTemplate(@"select i.*,s.StorageName from StorageItem i LEFT JOIN StorageInfo s ON i.StorageID=s.ID where /**where**/");
            sql.Where("i.UnionID=@0 and i.ShopID=@1 and i.GoodsID=@2", unionID, shopId, goodsId);
            return Db.Instance.Fetch<Dictionary<string, object>>(sqlTpl);
        }
        public static StorageItem GetQty(Guid shopId, Guid goodsId, Guid StorageID)
        {
            var Item = Db.Instance.Query<StorageItem>().Where(x => (x.ShopID == shopId && x.GoodsID == goodsId && x.StorageID == StorageID)).SingleOrDefault();
            return Item;
        }

        /// <summary>
        /// 库存分布查询
        /// </summary>
        /// <param name="UnionID"></param>
        /// <param name="ShopID"></param>
        /// <param name="Category"></param>
        /// <param name="GoodsWhere"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderField"></param>
        /// <returns></returns>
        public static Page<Dictionary<string, object>> DistributionQuery(out List<Storage> storages, Guid UnionID, Guid ShopID, string GoodsWhere, int pageNumber = 1, int pageSize = 50, string orderField = null)
        {
            var sql = new SqlBuilder();
            var sqlTpl = sql.AddTemplate(@"select g.*,isnull(OccupiedInventory,0) as OccupiedInventory from GoodsInfo g with(nolock)
        left join (
        select bp.GoodsID,SUM(SaleQty) as OccupiedInventory from BillPartDetail bp 
        left join billsheet bs on  bp.SheetID=bs.ID
        where bs.IsFinished=0 and ((bs.SheetType=1 and bs.SubType=2) or (bs.SheetType=1 and SubType=0))
         group by bp.GoodsID) as b on g.ID=b.GoodsID 
         where /**where**/ /**orderby**/");
            sql.Where("UnionID=@0  and Shopid=@1 and IsDeleted=0", UnionID, ShopID);

            if (!string.IsNullOrWhiteSpace(GoodsWhere)) sql.Where("(GoodsCode  like @0 or Spelling  like @0 or ShortSpelling  like @0  or GoodsName  like @0  or Brand  like @0 or MatchPlateNumber like @0  or MatchCarModel  like @0 or Location like @0 )", "%" + GoodsWhere + "%");
            if (!string.IsNullOrWhiteSpace(orderField))
            {
                sql.OrderBy(orderField);
            }
            Page<Dictionary<string, object>> result = null;
            if (pageNumber == -1 || pageSize == -1)
            {
                var list = Db.Instance.Fetch<Dictionary<string, object>>(sqlTpl);
                result = new Page<Dictionary<string, object>>() { CurrentPage = 1, ItemsPerPage = list.Count, TotalPages = 1, TotalItems = list.Count, Items = list };
            }
            else
            {
                result = Db.Instance.Page<Dictionary<string, object>>(pageNumber, pageSize, sqlTpl);
            }
            var listItems = new List<StorageItem>();
            var storageQtySql = @"
 select i.GoodsID,i.StorageID,SUM(i.Qty) as Qty
 from StorageItem i  with(nolock)  left join GoodsInfo g on i.GoodsID=g.ID
 where /**where**/
 group by i.GoodsID,i.StorageID";
            var sql1 = new SqlBuilder();
            var sqlTpl1 = sql1.AddTemplate(storageQtySql);
            sql1.Where("g.UnionID=@0  and g.Shopid=@1 and g.IsDeleted=0", UnionID, ShopID);
            if (!string.IsNullOrWhiteSpace(GoodsWhere)) sql.Where("(g.GoodsCode  like @0 or g.Spelling  like @0 or g.ShortSpelling  like @0  or g.GoodsName  like @0  or g.Brand  like @0 or g.MatchPlateNumber like @0  or g.MatchCarModel  like @0 )", "%" + GoodsWhere + "%");
            if (pageNumber == -1 || pageSize == -1)
            {
                listItems.AddRange(Db.Instance.Fetch<StorageItem>(sqlTpl1));
            }
            else
            {
                if (result.Items.Count > 0)
                {
                    sql1.Where(string.Format("g.ID in ('{0}')", string.Join("','", result.Items.Select(x => x["ID"].ToString()))));
                    listItems.AddRange(Db.Instance.Fetch<StorageItem>(sqlTpl1));
                }
            }
            var dictItems = listItems.ToDictionary(k => k.GoodsID.ToString() + k.StorageID.ToString(), v => v.Qty);

            storages = StorageService.GetStorage(UnionID, ShopID);
            var Qty=0M;
            foreach (var goods in result.Items)
            {
                var qtylist = new List<Dictionary<string, decimal>>();
                var Sum=0M;
                foreach (var i in storages)
                {
                    dictItems.TryGetValue(goods["ID"].ToString() + i.ID.ToString(), out Qty);
                    var qty = new Dictionary<string, decimal>();
                    qty.Add("Qty", Qty);
                    Sum+=Qty;
                    qtylist.Add(qty);
                }
                goods.Add("Qty", qtylist);
                goods.Add("QtySum", Sum);
                goods.Add("TotalMoney", Sum *(decimal)goods["CostPrice"]);
            }
            return result;
        }

        /// <summary>
        /// 库存分布查询合计
        /// </summary>
        /// <param name="UnionID"></param>
        /// <param name="ShopID"></param>
        /// <param name="Category"></param>
        /// <param name="GoodsWhere"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderField"></param>
        /// <returns></returns>
        public static decimal DistributionQueryAll(Guid UnionID, Guid ShopID, string GoodsWhere)
        {
            var sql = new SqlBuilder();
            var sqlTpl = sql.AddTemplate(@"select * from GoodsInfo where /**where**/ /**orderby**/");
            sql.Where("UnionID=@0  and Shopid=@1 and IsDeleted=0", UnionID, ShopID);

            if (!string.IsNullOrWhiteSpace(GoodsWhere)) sql.Where("(GoodsCode  like @0 or Spelling  like @0 or ShortSpelling  like @0  or GoodsName  like @0  or Brand  like @0 or MatchPlateNumber like @0  or MatchCarModel  like @0 )", "%" + GoodsWhere + "%");
           
            Page<Dictionary<string, object>> result = null;
         
                var list = Db.Instance.Fetch<Dictionary<string, object>>(sqlTpl);
                result = new Page<Dictionary<string, object>>() { CurrentPage = 1, ItemsPerPage = list.Count, TotalPages = 1, TotalItems = list.Count, Items = list };
          
            var listItems = new List<StorageItem>();
            var storageQtySql = @"
 select i.GoodsID,i.StorageID,SUM(i.Qty) as Qty
 from StorageItem i  with(nolock)  left join GoodsInfo g on i.GoodsID=g.ID
 where /**where**/
 group by i.GoodsID,i.StorageID";
            var sql1 = new SqlBuilder();
            var sqlTpl1 = sql1.AddTemplate(storageQtySql);
            sql1.Where("g.UnionID=@0  and g.Shopid=@1 and g.IsDeleted=0", UnionID, ShopID);
            if (!string.IsNullOrWhiteSpace(GoodsWhere)) sql.Where("(g.GoodsCode  like @0 or g.Spelling  like @0 or g.ShortSpelling  like @0  or g.GoodsName  like @0  or g.Brand  like @0 or g.MatchPlateNumber like @0  or g.MatchCarModel  like @0 )", "%" + GoodsWhere + "%");
           
                listItems.AddRange(Db.Instance.Fetch<StorageItem>(sqlTpl1));
         
            var dictItems = listItems.ToDictionary(k => k.GoodsID.ToString() + k.StorageID.ToString(), v => v.Qty);

            var storages = StorageService.GetStorage(UnionID, ShopID);
            var Qty = 0M;
            decimal TotalAll = 0; 
            foreach (var goods in result.Items)
            {
                var qtylist = new List<Dictionary<string, decimal>>();
                var Sum = 0M;
                foreach (var i in storages)
                {
                    dictItems.TryGetValue(goods["ID"].ToString() + i.ID.ToString(), out Qty);
                    var qty = new Dictionary<string, decimal>();
                    qty.Add("Qty", Qty);
                    Sum += Qty;
                    qtylist.Add(qty);
                }
                //goods.Add("Qty", qtylist);
                //goods.Add("QtySum", Sum);
                //goods.Add("TotalMoney", Sum * (decimal)goods["CostPrice"]);
                TotalAll += Sum * (decimal)goods["CostPrice"];
            }
            return TotalAll;
        }
    }
}
